package edu.uc.persistence;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import edu.uc.dto.Restaurant;


/**
 * Creates the general user functions.
 * 
 * >> 
 * >> fetchReview (searches database table REVIEWS for review object with matching ID)
 * >> getReviews (pulls all restaurants from database into a hashmap)
 * >> 
 * >> 
 * 
 * @author Robert
 *
 */
public class RestaurantDAO extends SQLiteOpenHelper implements RestaurantDAOInterface {

	// Define String Variables
	private static final String RESTAURANTS = "restaurants";
	private static final String SQL_ID = "_id";
	private static final String RESTAURANT_ID = "restaurantId";
	private static final String RESTAURANT_NAME = "restaurantName";
	private static final String RESTAURANT_ADDRESS = "restaurantAddress";
	private static final String RESTAURANT_CITY = "restaurantCity";
	private static final String RESTAURANT_NEIGHBORHOOD = "restaurantNeighborhood";
	private static final String RESTAURANT_STATE = "restaurantState";
	private static final String RESTAURANT_POSTALCODE = "restaurantPostalCode";
	private static final String RESTAURANT_COUNTRY = "restaurantCountry";
	private static final String RESTAURANT_LATITUDE = "restaurantLatitude";
	private static final String RESTAURANT_LONGITUDE = "restaurantLongitude";
	private static final String RESTAURANT_TELEPHONE = "restaurantTelephone";

	// Define DB Variables
	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "foodview";
	private final String CLASS_NAME = getClass().getName();
	
	// Declare new object
	RestaurantDAO testNew;

	// Create Context
	public RestaurantDAO(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		// CREATE TABLE statement
		String createReviewDB = "CREATE TABLE "+RESTAURANTS+" ("+SQL_ID+" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +RESTAURANT_ID+ " TEXT NOT NULL, "+RESTAURANT_NAME+" TEXT NOT NULL, "+RESTAURANT_ADDRESS+" TEXT NOT NULL, "+RESTAURANT_CITY+" TEXT NOT NULL, "+RESTAURANT_NEIGHBORHOOD+" TEXT NOT NULL, "+RESTAURANT_STATE+" TEXT NOT NULL, "+RESTAURANT_POSTALCODE+" TEXT NOT NULL, "+RESTAURANT_COUNTRY+" TEXT NOT NULL, "+RESTAURANT_LATITUDE+" TEXT NOT NULL, "+RESTAURANT_LONGITUDE+" TEXT NOT NULL, "+RESTAURANT_TELEPHONE+" TEXT NOT NULL);";
		Log.d(CLASS_NAME, createReviewDB);

		// Execute statement
		db.execSQL(createReviewDB);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public List<Restaurant> getRestaurants() throws Exception {

		// Create the return hash
		List<Restaurant> allRestaurants = new ArrayList<Restaurant>();

		// SQL statement
		String selectSQL = "SELECT * FROM " + RESTAURANTS ;

		// Log statement
		Log.d(CLASS_NAME, selectSQL);

		// Create the result set
		Cursor cursor = getReadableDatabase().rawQuery(selectSQL, null);

		// Iterate over the results
		if (cursor.getCount() > 0) {
			Log.i(CLASS_NAME, cursor.getCount() + " results returned");

			// Move the cursor to the very first result.
			cursor.moveToFirst();

			// Iterate over our results.  Keep iterating as long as we're not after the last row.
			while(!cursor.isAfterLast()) {

				// Define user object
				Restaurant thisRestaurant = new Restaurant();

				// populate
				thisRestaurant.setSqlId(cursor.getLong(0));
				thisRestaurant.setRestaurantId(cursor.getString(1));
				thisRestaurant.setRestaurantName(cursor.getString(2));
				thisRestaurant.setRestaurantAddress(cursor.getString(3));
				thisRestaurant.setRestaurantCity(cursor.getString(4));
				thisRestaurant.setRestaurantNeighborhood(cursor.getString(5));
				thisRestaurant.setRestaurantState(cursor.getString(6));
				thisRestaurant.setRestaurantPostalCode(cursor.getString(7));
				thisRestaurant.setRestaurantCountry(cursor.getString(8));
				thisRestaurant.setRestaurantLatitude(cursor.getString(9));
				thisRestaurant.setRestaurantLongitude(cursor.getString(10));
				thisRestaurant.setRestaurantTelephone(cursor.getString(11));

				// Add the user
				allRestaurants.add(thisRestaurant);

				// Log the user
				Log.d(CLASS_NAME, thisRestaurant.toString());

				// Continue the loop
				cursor.moveToNext();

			}
			// Close the process at the end
			cursor.close();

		} else {
			Log.i(CLASS_NAME, "No results returned");			
		}

		return allRestaurants;
	}

	@Override
	public Restaurant fetchRestaurant(String Latitude, String Longitude) throws Exception {
		// create a return value.
		Restaurant FetchedRestaurant = new Restaurant();

		// the SQL to retrieve plants from our database.
		String selectSQL = "SELECT * FROM " + RESTAURANTS + " WHERE " + RESTAURANT_LATITUDE + "='" + Latitude + "' and " + RESTAURANT_LONGITUDE + "='" + Longitude + "'";

		// log the sql statement
		Log.d(CLASS_NAME, selectSQL);

		// get a cursor, which is like a ResultSet.  It allows us to iterate over results.
		Cursor cursor = getReadableDatabase().rawQuery(selectSQL, null);

		// iterate over results.
		if (cursor.getCount() > 0) {
			Log.i(CLASS_NAME, cursor.getCount() + " results returned");
			// move the cursor to the very first result.
			cursor.moveToFirst();

			// populate
			FetchedRestaurant.setSqlId(cursor.getLong(0));
			FetchedRestaurant.setRestaurantId(cursor.getString(1));
			FetchedRestaurant.setRestaurantName(cursor.getString(2));
			FetchedRestaurant.setRestaurantAddress(cursor.getString(3));
			FetchedRestaurant.setRestaurantCity(cursor.getString(4));
			FetchedRestaurant.setRestaurantNeighborhood(cursor.getString(5));
			FetchedRestaurant.setRestaurantState(cursor.getString(6));
			FetchedRestaurant.setRestaurantPostalCode(cursor.getString(7));
			FetchedRestaurant.setRestaurantCountry(cursor.getString(8));
			FetchedRestaurant.setRestaurantLatitude(cursor.getString(9));
			FetchedRestaurant.setRestaurantLongitude(cursor.getString(10));
			FetchedRestaurant.setRestaurantTelephone(cursor.getString(11));

			// close the cursor.
			cursor.close();

		} else {
			Log.i(CLASS_NAME, "No results returned");			
		}

		return FetchedRestaurant;
	}
}
